{% extends "ceudp/base.html" %}
{% load staticfiles %}

{% block customize-style %}
    <style type="text/css">
        .icon {
            margin-right: 3px;
        }

        .nav-tabs-custom > .nav-tabs > li.active {
            border-top-color: #00a65a;
        }

        .nav-tabs-custom > .tab-content {
            /*height: 200px;*/
        }

        .query-result-table {
            width: 100%;  
        }

    </style>
{% endblock %}

{% block customize-script %}
    <script>
        leaf_active("sql_lab"); // 激活左侧菜单栏图标效果

        var code_editor = null;
        var query_history_table = null; 
        var query_result_table = null;
        var page_number = 1;

        $(function(){
            $(".select2").select2({
                placeholder: {
                    id: '-1', // the value of the option
                    text: 'Select an option'
                }
            }); 

            query_history_table = $('#query-history-table').DataTable({
                "language": {
                    "processing": '<div class="overlay"><i class="fa fa-refresh fa-spin"></i></div>',
                    "loadingRecords": "加载中...",
                    "info": "共 _TOTAL_ 条",
                    "infoEmpty": "共 0 条",
                    "zeroRecords": "无数据",
                    "emptyTable": "无数据",
                    "search": "<i class='fa fa-search'></i>",
                    "paginate": {
                        "first": "第一页",
                        "previous": "上一页",
                        "next": "下一页",
                        "last": "最后一页",
                    },
                },
                "processing": true,
                "searching": true,
                "ordering": false,
                "bLengthChange": false, // 是否启动可以改变单页数量
                "scrollCollapse": true,
                "iDisplayLength" : 10, // 设置单页显示数量
                "ajax": {
                    "url": "{% url 'performance_api:query-list' %}",
                    "method": "GET",
                    "timeout": 5000,
                    "dataSrc": function(response){ return response },
                    "error": function(response){
                        sweetAlert("错误", response.responseText, "error");
                    }
                },
                "columns": [
                    { 
                        "data": "status", "title": "状态", "width": "30px",
                        "render": function(status){
                            switch (status) {
                                case "0":
                                    return "<span class='label label-success'>成功</span>";
                                    break;
                                case "-1":
                                    return "<span class='label label-danger'>失败</span>";
                                    break;
                                default:
                                    return "<span class='label label-default'>未知</span>";
                            }
                        },
                    },
                    { 
                        "data": "created", "title": "创建时间", "width": "90px",
                        "render": function(created){
                            var date = new Date(created);
                            return date.toLocaleDateString() + " " + date.toLocaleTimeString();
                        },
                    },
                    { 
                        "data": "query", "title": "SQL", 
                    },
                    { 
                        "data": "duration", "title": "耗时", "width": "30px",
                        "render": function(duration){
                            if (duration != 0)
                                duration = duration.toFixed(2);

                            return duration;
                        },
                    },
                    { 
                        "data": "rows", "title": "总数", "width": "30px",
                    },
                    { 
                        "data": "page_number", "title": "页数", "width": "30px",
                    },
                ],
            });

            code_editor = CodeMirror.fromTextArea(document.getElementById('sql_editer'), {
                mode: "text/x-hive",
                theme: "monokai",
                indentWithTabs: true,
                smartIndent: true,
                lineNumbers: true,
                matchBrackets : true,
                autofocus: true,
                // extraKeys: {"Ctrl-Space": "autocomplete"},
                hintOptions: {
                    tables: {
                        users: {name: null, score: null, birthDate: null},
                        countries: {name: null, population: null, size: null}
                    }
                }
            }); 
             
            $("#execute_query_btn").on("click", function(){
                page_number = 1;
                // 执行SQL查询
                executed_query(code_editor.getValue(), page_number);
            });
        });

        // 重新生成表格
        function bulid_query_result_table(columns, data, response_set) {
            var columns_arr = [];
            for (var i = 0; i < columns.length; i++){
                columns_arr.push({"data": columns[i], "title": columns[i]})
            }

            if (query_result_table != null) {
                query_result_table.destroy();
                $("#query-result-table").empty();
            }

            query_result_table = $("#query-result-table").DataTable({
                "destroy": true,
                "columns": columns_arr,
                "data": data,
                "language": {
                    "processing": '<div class="overlay"><i class="fa fa-refresh fa-spin"></i></div>',
                    "loadingRecords": "加载中...",
                    "info": "共 _TOTAL_ 条",
                    "infoEmpty": "共 0 条",
                    "zeroRecords": "无数据",
                    "emptyTable": "无数据",
                    "search": "<i class='fa fa-search'></i>",
                    "paginate": {
                        "first": "第一页",
                        "previous": "上一页",
                        "next": "下一页",
                        "last": "最后一页",
                    },
                },
                "processing": true,
                "info": false,
                "paging": false,
                "searching": false,
                "ordering": false,
                "bLengthChange": false, // 是否启动可以改变单页数量
                "scrollCollapse": true,
                "iDisplayLength" : 10, // 设置单页显示数量
                "scrollX": true,
                "dom": "<'#top-toolbar'>lrtip<'#pagination-toolbar'>",
            });
            
            // 分页按钮
            var pagination_toolbar = "<div class='box-footer'>" +
                "共<span id='total'> " + response_set.total + " </span>条" +
                "<div class='btn-group pull-right'>" +
                    "<button id='previous' type='button' class='btn btn-default'>上一页</button>" +
                    "<button id='next' type='button' class='btn btn-default'>下一页</button>" +
                "</div>" +
            "</div>";

            // 顶部按钮
            var top_toolbar = "<div class='box-header'>" +
                "<div class='btn-group pull-right'>" +
                    // "<button type='button' class='btn btn-primary'><i class='fa fa-save'></i></button>" +
                    "<button type='button' class='btn btn-danger btn-xs' data-toggle='tooltip' title='下载'><i class='fa fa-download'></i></button>" +
                "</div>" +
            "</div>";
            
            $("#pagination-toolbar").html(pagination_toolbar);
            $("#top-toolbar").html(top_toolbar);

            // 判断若是第一页，则设置上一页为失效
            if (page_number == 1) {
                var previous_btn = $("div#pagination-toolbar button#previous");
                previous_btn.addClass("disabled"); // 设置失效效果
                previous_btn.attr("disabled", "true"); // 设置按钮不能点
            }

            // 判断若是最后一页，则设置下一页为失效
            if (page_number * 10 >= response_set.total) {
                var next_btn = $("div#pagination-toolbar button#next");
                next_btn.addClass("disabled"); // 设置失效效果
                next_btn.attr("disabled", "true"); // 设置按钮不能点
            }

            $("div#pagination-toolbar button#previous").on("click", function(){
                if (page_number != 1) page_number = page_number - 1;
                executed_query(code_editor.getValue(), page_number);
            });

            $("div#pagination-toolbar button#next").on("click", function(){
                page_number = page_number + 1;
                executed_query(code_editor.getValue(), page_number);
            });
        }

        // 开启遮罩
        function append_overlay() {
            $("div.overlay").each(function(){
                $(this).css("display", "block");
            });
        }

        // 关闭遮罩
        function remove_overlay() {
            $("div.overlay").each(function(){
                $(this).css("display", "none");
            });
        }

        // 执行SQL查询
        function executed_query(sql_query, page_number) {
            // 开启遮罩
            console.info("开启遮罩");
            append_overlay();
            console.info(sql_query, page_number);           
            // 同步 Ajax
            $.ajax({
                type: "POST",
                url: "{% url 'performance_api:query-list' %}",
                data: {
                    "query": sql_query, 
                    "page_number": page_number,
                    "csrfmiddlewaretoken": "{{ csrf_token }}",},
                async: false,
                success: function(response){
                    var response_set = response.response_set;
                    var columns = response_set.columns;
                    var data = response_set.data;
                    console.info(columns, data, response_set);
                    bulid_query_result_table(columns, data, response_set);
                },
                error: function(response){
                    sweetAlert("错误", response.responseText, "error");
                },
            });
            // 切换到查询结果
            active_pane("query-results");
            // 重新过去查询历史记录
            query_history_table.ajax.reload();            
            // 关闭遮罩
            console.info("关闭遮罩");
            remove_overlay();
        }

        function active_pane(id) {
            $("div#query-results-box div.nav-tabs-custom ul.nav-tabs li").each(function(){
                $(this).removeClass("active");
            });

            $("div.tab-content div.tab-pane").each(function(){
                $(this).removeClass("active");
            });

            $("#" + id + "-tab").addClass("active");
            $("#" + id + "-pane").addClass("active");
        }

    </script>
{% endblock %}

{% block content %}
    <div class="content-wrapper">
        <section class="content-header">
            <h1><i class="fa fa-flask"></i> SQL Lab</h1>
            <ol class="breadcrumb">
                <li>
                    <a href="{% url 'dashboard:index' %}"><i class="fa fa-dashboard"></i> 仪表盘 <small>[Dashboard]</small> </a>
                </li>
                <li>
                    <a href="#"><i class="fa fa-area-chart"></i>数据展示 <small>[Performance]</small></a>
                </li>
                <li class="active">
                    <i class="fa fa-flask"></i> SQL Lab
                </li>
            </ol>
        </section>
        <section class="content">
            <div class="row">
                <!-- 数据库&表 -->
                <!-- <div class="col-md-4">
                    <div class="box box-danger">
                        <div class="box-header with-border">
                            <h3 class="box-title">数据库 & 表 <small> [Databases & Tables]</small> </h3>
                            <div class="box-tools pull-right">
                                <button type="button" class="btn btn-box-tool" data-widget="collapse">
                                    <i class="fa fa-minus"></i>
                                </button>
                            </div>
                        </div>
                        <div class="box-body">
                            <div class="form-group">
                                <select class="form-control select2" style="width: 100%;">
                                    <option selected="selected"><i class="icon fa fa-database"></i>Alabama</option>
                                    <option><i class="icon fa fa-database"></i>Alaska</option>
                                    <option><i class="icon fa fa-database"></i>California</option>
                                    <option><i class="icon fa fa-database"></i>Delaware</option>
                                    <option><i class="icon fa fa-database"></i>Tennessee</option>
                                    <option><i class="icon fa fa-database"></i>Texas</option>
                                    <option><i class="icon fa fa-database"></i>Washington</option>
                                </select>
                            </div>
                            <div class="form-group">
                                <select class="form-control select2" style="width: 100%;">
                                    <option selected="selected"><i class="icon fa fa-database"></i>Alabama</option>
                                    <option><i class="icon fa fa-database"></i>Alaska</option>
                                    <option><i class="icon fa fa-database"></i>California</option>
                                    <option><i class="icon fa fa-database"></i>Delaware</option>
                                    <option><i class="icon fa fa-database"></i>Tennessee</option>
                                    <option><i class="icon fa fa-database"></i>Texas</option>
                                    <option><i class="icon fa fa-database"></i>Washington</option>
                                </select>
                            </div>
                            <div class="form-group">
                                <select class="form-control select2" disabled="disabled" style="width: 100%;">
                                    <option selected="selected">Alabama</option>
                                    <option>Alaska</option>
                                    <option>California</option>
                                    <option>Delaware</option>
                                    <option>Tennessee</option>
                                    <option>Texas</option>
                                    <option>Washington</option>
                                </select>
                            </div>
                        </div>
                    </div>
                    
                    <div class="box box-warning">
                        <div class="box-header with-border">
                            <h3 class="box-title">表结构 <small> [Table Columns]</small></h3>
                            <div class="box-tools pull-right">
                                <button type="button" class="btn btn-box-tool" data-widget="collapse">
                                    <i class="fa fa-minus"></i>
                                </button>
                            </div>
                        </div>
                        <div class="box-body" style="height: 300px;">

                        </div>
                    </div>
                </div> -->

                <div class="col-md-12">
                    <!-- SQL编辑器 -->
                    <div class="box box-info" id="sql-editer-box">
                        <div class="box-header with-border">
                            <h3 class="box-title">编辑器 <small>[SQL Editer]</small></h3>
                            <div class="box-tools pull-right">
                                <button type="button" class="btn btn-box-tool" data-widget="collapse">
                                    <i class="fa fa-minus"></i>
                                </button>
                            </div>
                        </div>
                        <div class="box-body">
                            <textarea id="sql_editer" name="sql_editer"></textarea>
                        </div>
                        <div class="box-footer">
                            <button type="button" class="btn btn-flat btn-sm btn-success" id="execute_query_btn"> 
                                <i class="icon fa fa-chevron-right"></i> 
                                执行查询
                            </button>
                            <!-- <button type="button" class="btn btn-flat btn-sm btn-primary" style="margin-left: 5px;"> 
                                <i class="icon fa fa-lg fa-save"></i> 
                                保存查询
                            </button> -->
                        </div>
                        <div class="overlay" style="display: none;">
                            <i class="fa fa-refresh fa-spin"></i>
                        </div>
                    </div>
                    <!-- SQL编辑器 -EOF- -->
                    <!-- 查询结果 -->
                    <div class="box box-solid" id="query-results-box">
                        <div class="nav-tabs-custom">
                            <ul class="nav nav-tabs">
                                <!-- <li class="active" id="preview-tab">
                                    <a href="#preview-pane" data-toggle="tab">
                                        <i class="icon fa fa-search"></i>
                                        预览 <small>[Preview]</small>
                                    </a>
                                </li> -->
                                <li id="query-results-tab">
                                    <a href="#query-results-pane" data-toggle="tab">
                                        <i class="icon fa fa-table"></i>
                                        查询结果 <small>[Results]</small>
                                    </a>
                                </li>
                                <li class="active" id="query-history-tab">
                                    <a href="#query-history-pane" data-toggle="tab">
                                        <i class="icon fa fa-history"></i>
                                        历史查询 <small>[History]</small>
                                    </a>
                                </li>
                            </ul>
                            <div class="tab-content">
                                <!-- <div class="tab-pane active" id="preview-pane">
                                    <table id="query-preview-table" class="table table-striped table-bordered">无数据</table>
                                </div> -->
                                <div class="tab-pane" id="query-results-pane">
                                    <table id="query-result-table" class="table table-striped table-bordered query-result-table"></table>
                                </div>
                                <div class="tab-pane active" id="query-history-pane">
                                    <table id="query-history-table" class="table table-striped table-bordered">
                                        <thead>
                                            <tr>
                                                <th>status</th>
                                                <th>created</th>
                                                <th>duration</th>
                                                <th>total</th>
                                                <th>page_number</th>
                                                <th>query</th>
                                            </tr>
                                        </thead>
                                    </table>
                                </div>
                            </div>
                        </div>
                        <div class="overlay" id="a_overlay" style="display: none;">
                            <i class="fa fa-refresh fa-spin"></i>
                        </div>
                    </div>
                    <!-- 查询结果 -EOF- -->
                </div>
            </div>
        </section>
    </div>
{% endblock %}